const express = require("express");
const router = express.Router();
const dayjs = require("dayjs");
const utc = require("dayjs/plugin/utc");
const timezone = require("dayjs/plugin/timezone");

dayjs.extend(utc);
dayjs.extend(timezone);
let connectDAO = require("./connect.js");

// 登陆
router.get("/login", function (req, res) {
  const { PhoneNumber, Password } = req.query;
  const sql = `SELECT * FROM Officers WHERE PhoneNumber = '${PhoneNumber}' AND Password = '${Password}';`;
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.length > 0) {
      res.send({
        code: 200,
        msg: "Success",
        data: records[0],
      });
    } else {
      res.send({
        code: 201,
        msg: "账号或密码不正确",
      });
    }
  });
});

// 注册
router.post("/register", function (req, res) {
  const { Name, Department, PhoneNumber } = req.body;
  const sql = `INSERT INTO Officers (Name, Gender,Rank,Department,PhoneNumber, Permission,Password,HireDate) VALUES 
  ('${Name}', 'Male', '警员', '${
    Department || "派出所"
  }', '${PhoneNumber}', 'user','${PhoneNumber.slice(-6)}', '${dayjs().format(
    "YYYY-MM-DD"
  )}')`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

// 根据id获取仪容仪表列表
router.get("/get_appearance_by_id", function (req, res) {
  const { RecordId } = req.query;
  const sql = `SELECT * FROM AppearanceRecords a JOIN Officers o ON a.OfficerID = o.OfficerID WHERE a.RecordID = '${RecordId}'`;
  connectDAO
    .queryDataBySQL(sql, (records) => {
      if (records.length > 0) {
        res.send({
          code: 200,
          msg: "Success",
          records: records[0],
        });
      } else {
        res.send({
          code: 200,
          msg: "no data",
          records,
        });
      }
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

// 获取仪容仪表列表
router.get("/get_appearance_records", function (req, res) {
  const { OfficerID } = req.query;
  let sql = `SELECT 
    o.OfficerID,
    o.Name,
    o.Department,
    a.CheckDate,
    a.AppearanceScore,
    a.UniformScore,
    a.Comments,
     a.RecordId,
     a.FileList
FROM 
    AppearanceRecords a
JOIN 
    Officers o ON a.OfficerID = o.OfficerID
    ${OfficerID ? ` WHERE o.OfficerID = '${OfficerID}'` : ""}
ORDER BY 
    a.CheckDate DESC, o.Name
    `;
  connectDAO
    .queryDataBySQL(sql, (records) => {
      if (records.length > 0) {
        res.send({
          code: 200,
          msg: "Success",
          records,
          total: records.length,
        });
      } else {
        res.send({
          code: 200,
          msg: "no data",
          records,
        });
      }
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

// 新增仪容仪表
router.post("/add_appearance", function (req, res) {
  const {
    OfficerID,
    CheckDate,
    AppearanceScore,
    UniformScore,
    Comments,
    FileList,
  } = req.body;
  const sql = `INSERT INTO AppearanceRecords (OfficerID, CheckDate, AppearanceScore, UniformScore, Comments,FileList) VALUES 
  ('${OfficerID}', '${CheckDate}', '${AppearanceScore}', '${UniformScore}', '${Comments}','${FileList}')`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

// 删除仪容仪表
router.delete("/delete_appearance", (req, res) => {
  const { RecordId } = req.query;
  connectDAO
    .queryDataBySQL(
      `DELETE FROM AppearanceRecords WHERE RecordID = '${RecordId}'`
    )
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

// 获取值班记录
router.get("/get_duty_records", function (req, res) {
  const sql = `SELECT * FROM DutyRoster`;
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.length > 0) {
      res.send({
        code: 200,
        msg: "Success",
        records,
        total: records.length,
      });
    } else {
      res.send({
        code: 200,
        msg: "no data",
        records,
      });
    }
  });
});

// 新增值班记录
router.post("/add_duty", function (req, res) {
  const { OfficerID, DutyDate, OfficerName } = req.body;
  const sql = `INSERT INTO DutyRoster (OfficerID, DutyDate,OfficerName) VALUES 
  ('${OfficerID}', '${DutyDate}', '${OfficerName}')`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

// 获取請假记录
router.get("/get_holidays_records", function (req, res) {
  const { OfficerID } = req.query;
  const sql = `SELECT l.*,o.Name AS OfficerName FROM LeaveRequests l JOIN Officers o ON l.OfficerID = o.OfficerID ${
    OfficerID ? ` WHERE o.OfficerID = '${OfficerID}'` : ""
  } ORDER BY l.StartDate DESC`;
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.length > 0) {
      res.send({
        code: 200,
        msg: "Success",
        records,
        total: records.length,
      });
    } else {
      res.send({
        code: 200,
        msg: "no data",
        records,
      });
    }
  });
});

// 新增請假记录
router.post("/add_holidays", function (req, res) {
  const { OfficerID, LeaveType, StartDate, EndDate, Reason, Status } = req.body;
  const sql = `INSERT INTO LeaveRequests (OfficerID, LeaveType,StartDate,EndDate,Reason,Status) VALUES 
  ('${OfficerID}', '${LeaveType}', '${StartDate}','${EndDate}','${Reason}','${Status}')`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

// 审批请假记录
router.put("/put_holidays", function (req, res) {
  const { LeaveID, Status } = req.body;
  const sql = `UPDATE LeaveRequests SET Status = '${Status}' WHERE LeaveID = '${LeaveID}'`;
  connectDAO.queryDataBySQL(sql).then(() => {
    res.send({ code: 200, msg: "Success" });
  });
});

// 获取采购记录
router.get("/get_purchase_records", function (req, res) {
  const { OfficerID } = req.query;
  const sql = `SELECT l.*,o.Name AS OfficerName FROM BudgetExpenditures l JOIN Officers o ON l.OfficerID = o.OfficerID ${
    OfficerID ? ` WHERE o.OfficerID = '${OfficerID}'` : ""
  }  ORDER BY l.Date DESC`;
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.length > 0) {
      res.send({
        code: 200,
        msg: "Success",
        records,
        total: records.length,
      });
    } else {
      res.send({
        code: 200,
        msg: "no data",
        records,
      });
    }
  });
});

// 新增采购记录
router.post("/add_purchase", function (req, res) {
  const { OfficerID, Category, Amount, Date, Description, Status } = req.body;
  const sql = `INSERT INTO BudgetExpenditures (OfficerID, Category, Amount, Date, Description,Status) VALUES 
    ('${OfficerID}', '${Category}', '${Amount}', '${Date}', '${Description}','${Status}')`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

// 审批采购记录
router.put("/put_purchase", function (req, res) {
  const { ExpenditureID, Status } = req.body;
  const sql = `UPDATE BudgetExpenditures SET Status = '${Status}' WHERE ExpenditureID = '${ExpenditureID}'`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

// 获取涉案记录
router.get("/get_cases_records", function (req, res) {
  const sql = `SELECT l.*,o.Name AS OfficerName FROM Cases l JOIN Officers o ON l.OfficerInCharge = o.OfficerID ORDER BY l.StartDate DESC`;
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.length > 0) {
      res.send({
        code: 200,
        msg: "Success",
        records,
        total: records.length,
      });
    } else {
      res.send({
        code: 200,
        msg: "no data",
        records,
      });
    }
  });
});

// 获取涉案物品
router.get("/get_casesprop_records", function (req, res) {
  const { CaseID } = req.query;
  const sql = `SELECT * FROM CaseProperties WHERE CaseID = '${CaseID}'`;
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.length > 0) {
      res.send({
        code: 200,
        msg: "Success",
        records,
        total: records.length,
      });
    } else {
      res.send({
        code: 200,
        msg: "no data",
        records,
      });
    }
  });
});

// 新增涉案物品
router.post("/post_casesprop", function (req, res) {
  const { CaseID, Status, Description, StorageLocation } = req.body;
  const sql = `INSERT INTO CaseProperties (CaseID, Status, Description, StorageLocation) VALUES 
  ('${CaseID}', '${Status}', '${Description}', '${StorageLocation}')`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

// 获取officers
router.get("/get_officers_records", function (req, res) {
  const { OfficerID } = req.query;
  const sql = `SELECT * FROM Officers ${
    OfficerID ? "WHERE OfficerID = " + OfficerID : ""
  }`;
  connectDAO
    .queryDataBySQL(sql, (records) => {
      if (records.length > 0) {
        res.send({
          code: 200,
          msg: "Success",
          records,
          total: records.length,
        });
      } else {
        res.send({
          code: 200,
          msg: "no data",
          records,
        });
      }
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

module.exports = router;
